package cn.hd01.repository;

import java.math.BigDecimal;
import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import cn.hd01.repository.entity.COrder;

@Repository
public interface COrderRepository extends JpaRepository<COrder, Integer> {
	Page<COrder> findAll(Specification<COrder> spec, Pageable pageable);
	
	List<COrder> findAll(Specification<COrder> spec);
	
	COrder findByFinanceOrderNo(String financeOrderNo);
	
	Integer countByIsBalanceAndDistributorId(String isBalance, Integer distributorId);
	
	@Modifying
	@Transactional
	@Query(value="update COrder set is_balance = ?1, update_time = now() where id=?2")
	int balanceOrder(String balanceStatus, Integer id);
	
	@Query(value="SELECT SUM(IF(distributor_id = ?1, amount, 0)) amount, SUM(IF(recomm_distributor_id = ?1, amount, 0)) other_amount FROM c_order", nativeQuery=true)
	List<Object[]> findTotalAmount(Integer distributorId);
	
	@Query(value="select sum(amount), SUM(CASE WHEN isBalance = 0 THEN (servantRebate + distributorRebate) ELSE 0 END), SUM(CASE WHEN isBalance = 1 THEN (servantRebate + distributorRebate) ELSE 0 END) from COrder where create_time >= ?1 and create_time <= ?2")
	List<Object[]> findSummary(String beginDate, String endDate);
	
	@Query(value="select sum(amount), SUM(CASE WHEN isBalance = 0 THEN (servantRebate + distributorRebate) ELSE 0 END), SUM(CASE WHEN isBalance = 1 THEN (servantRebate + distributorRebate) ELSE 0 END) from COrder where create_time >= ?1")
	List<Object[]> findSummaryByBegin(String beginDate);
	
	@Query(value="select sum(amount), SUM(CASE WHEN isBalance = 0 THEN (servantRebate + distributorRebate) ELSE 0 END), SUM(CASE WHEN isBalance = 1 THEN (servantRebate + distributorRebate) ELSE 0 END) from COrder where create_time <= ?1")
	List<Object[]> findSummaryByEnd(String endDate);
	
	@Query(value="select sum(amount), SUM(CASE WHEN isBalance = 0 THEN (servantRebate + distributorRebate) ELSE 0 END), SUM(CASE WHEN isBalance = 1 THEN (servantRebate + distributorRebate) ELSE 0 END) from COrder")
	List<Object[]> findSummary();
	
	Integer countByServantIdAndIsBalance(Integer ServantId, String isBalance);
	
	Integer countByIsBalance(String isBalance);
	
	@Query(value="select SUM(o.amount) as achievementToday from COrder o WHERE to_days(o.createTime) = to_days(now()) ")
	Double sumAchievementToday();
	
	@Query(value="select SUM(o.amount) as achievementToday from COrder o WHERE date_format(o.createTime,'%Y-%m') = date_format(now(),'%Y-%m') ")
	Double sumAchievementMonth();
	
	@Query(value="select (SUM(o.amount)/(select mt.amount from MonthlyTarget mt where date_format(mt.targetTime,'%Y-%m') = date_format(o.createTime,'%Y-%m')))*100  from COrder o WHERE date_format(o.createTime,'%Y-%m')=date_format(now(),'%Y-%m') ")
	Double progressMonthlyTarget();
	
	@Query(value="select SUM(o.distributorRebate) as totalRebate from COrder o WHERE o.isBalance = ?1 and date_format(o.createTime,'%Y-%m')=date_format(now(),'%Y-%m') ")
	Double sumDistributorRebateRate(String isBalance);
	
	@Query(value="select SUM(o.distributorRebate + o.recommDistributorRebate) as totalRebate from COrder o WHERE date_format(o.createTime,'%Y-%m')=date_format(now(),'%Y-%m') ")
	Double sumDistributorRebateRate();
	
	@Query(value="select COUNT(distinct o.orderId) as operatCount  from CustomerOperation o WHERE date_format(o.operationTime,'%Y-%m')=date_format(now(),'%Y-%m') and status = '0'")
	Integer countOperationByMonth();
	
	@Query(value="select COUNT(distinct o.orderId) as operatCount  from CustomerOperation o WHERE to_days(o.operationTime) = to_days(now()) and status = '0' ")
	Integer countOperationByToday();
	
	@Query(value="SELECT ROUND(SUM(balanceKValue), 2) FROM (SELECT     SUM(     p.distributor_rebate    ) AS balanceKValue  FROM    c_order c     INNER JOIN c_order_payment p       ON c.id = p.order_id   WHERE c.distributor_id = ?1    AND p.is_balance = '0'  UNION  SELECT     SUM(        p.recomm_distributor_rebate    ) AS balanceKValue  FROM    c_order c     INNER JOIN c_order_payment p       ON c.id = p.order_id   WHERE c.distributor_id = ?1    AND p.is_balance = '0') temp", nativeQuery=true)
	Integer findBalanceKValue(Integer distributorId);
	
	@Query(value="SELECT ROUND(SUM(balanceKValue), 2) FROM (SELECT     SUM(     p.distributor_rebate    ) AS balanceKValue  FROM    c_order c     INNER JOIN c_order_payment p       ON c.id = p.order_id   WHERE c.distributor_id = ?1    AND p.is_balance = '1'  UNION  SELECT     SUM(        p.recomm_distributor_rebate    ) AS balanceKValue  FROM    c_order c     INNER JOIN c_order_payment p       ON c.id = p.order_id   WHERE c.distributor_id = ?1    AND p.is_balance = '1') temp", nativeQuery=true)
	Integer findUnBalanceKValue(Integer distributorId);
	
	@Query(value="SELECT ROUND(SUM(balanceKValue), 2), ROUND(SUM(unBalanceKValue), 2), kDate FROM  (SELECT SUM(IF(p.is_balance = 0, p.distributor_rebate, 0)) AS balanceKValue, SUM(IF(p.is_balance = 1, p.distributor_rebate, 0)) AS unBalanceKValue, DATE_FORMAT(p.create_time, '%Y-%c-%d') AS kDate  FROM c_order c INNER JOIN  c_order_payment p ON c.id = p.order_id WHERE c.distributor_id = ?1 AND p.create_time >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)  GROUP BY DATE_FORMAT(p.create_time, '%Y-%c-%d') UNION all SELECT SUM(IF(p.is_balance = 0, p.recomm_distributor_rebate, 0)) AS balanceKValue, SUM(IF(p.is_balance = 1, p.recomm_distributor_rebate, 0)) AS unBalanceKValue, DATE_FORMAT(p.create_time, '%Y-%c-%d') AS kDate  FROM c_order c INNER JOIN  c_order_payment p ON c.id = p.order_id WHERE c.recomm_distributor_id = ?1 AND p.create_time >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)  GROUP BY DATE_FORMAT(p.create_time, '%Y-%c-%d') ) t GROUP BY kDate ", nativeQuery=true)
	List<Object[]> findKValueGroupByDate(Integer distributorId);
	
	@Query(value="select SUM(amount) from COrder WHERE customer_id = ?1")
	BigDecimal findCustomerSumAmount(Integer customerId);
	
	@Query(value="SELECT SUM(IF(DATE_FORMAT(p.create_time, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d'), IFNULL(p.recomm_distributor_rebate, 0), 0)),	SUM(IF(p.is_balance = '0', IFNULL(p.recomm_distributor_rebate, 0), 0)), SUM(IF(p.is_balance = '1', IFNULL(p.recomm_distributor_rebate, 0), 0)) FROM c_order o LEFT JOIN c_order_payment p ON o.id = p.order_id WHERE o.recomm_distributor_id = ?1", nativeQuery=true)
	List<Object[]> findRecommKvalue(Integer distributorId);
}
